﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Threading;
using MySql.Data.MySqlClient;
using WpfApp1.bean;
using WpfApp1.mysqlunit;
/**数据库允许文件*/
namespace WpfApp1.mysqlunit
{
    class MysqlData
    {
        string M_str_sqlcon = "server=localhost;user id=root;password=zwp61380zwp;database=zupan;Allow User Variables=True";
        MySqlConnection mysqlcon;
        MySqlConnection mysqlconchaxun;
        private static MysqlData _MysqlData = null;

        public static MysqlData createMysqlData()
        {
            if (_MysqlData == null)
            {
                _MysqlData = new MysqlData();
                return _MysqlData;
            }
            else
            {
                return _MysqlData;
            }
        }

        //public MysqlData()
        //{
        //    openmysql();
        //}

        //private void openmysql()
        //{
        //    //打开数据库连接
        //    mysqlcon = new MySqlConnection(M_str_sqlcon);
        //    mysqlcon.Open();
        //}
        //读取账号信息
        public User readusersql()
        {
            var sql = "select * from user where id=1;";
            var dr = SELECT(sql);
            //如果有数据就输出
            var user = new User();
            if (dr.HasRows)
            {
                //逐行读取数据输出
                while (dr.Read())
                {
                    user.username = dr.GetString("username");
                    user.password = dr.GetString("password");
                    user.session_name = dr.GetString("session_name");
                    user.token = dr.GetString("token");
                    user.cookie = dr.GetString("cookie");
                }
            }
            dr.Close();
            if (mysqlconchaxun != null)
            {
                mysqlconchaxun.Close();
            }
            return user;
        }

        //查询数据库获取节号
        public List<string> readbatch(List<String> batchs)
        {
            batchs.Clear();
            var sql = "select batch from package where deliver_time is null AND warehousing_time is null GROUP BY batch;";

            var dr = SELECT(sql);
            if (dr.HasRows)
            {
                //逐行读取数据输出
                while (dr.Read())
                {

                    batchs.Add(dr.GetString("batch"));
                }
            }
            dr.Close();
            if (mysqlconchaxun != null)
            {
                mysqlconchaxun.Close();
            }
            return batchs;
        }

        //查询数据库获取未组盘包数据
        public Dictionary<String, packagebean> readpackage(Dictionary<String, packagebean> packages)
        {
            var sql = "select * from package where deliver_time is null AND pallet_num is null;";
            var dr = SELECT(sql);
            if (dr.HasRows)
            {
                //逐行读取数据输出
                while (dr.Read())
                {
                    var bean = new packagebean();
                    bean.batch = getDrString(dr, "batch");
                    bean.onum = getDrString(dr, "onum"); 
                    bean.pcode = getDrString(dr, "pcode"); 
                    bean.pid = getDrString(dr, "pid");
                    bean.pallet_id = getDrString(dr, "pallet_id");
                    bean.deliver_time = getDrString(dr, "deliver_time");
                    bean.zp_ctime = getDrString(dr, "zp_ctime");
                    bean.pcpid = getDrString(dr, "pcpid"); 
                    bean.warehousing_time = getDrString(dr, "warehousing_time");
                    packages.Add(bean.pcode, bean);
                }
            }
            dr.Close();
            if (mysqlconchaxun != null)
            {
                mysqlconchaxun.Close();
            }
            return packages;
        }

        public String getDrString(MySqlDataReader dr,String key)
        {
            if (dr[key] is DBNull)
            {
                return "";
            }
            return dr.GetString(key);
        }

        //读取位置处的盘
        public Dictionary<String, panbean> readpanonym(Dictionary<String, panbean> panonum)
        {
            var sql = "select * from pallet;";
            var dr = SELECT(sql);
            if (dr.HasRows)
            {
                //逐行读取数据输出
                while (dr.Read())
                {
                    var bean = new panbean();
                    bean.buffer = getDrString(dr,"buffer");
                    bean.onum = getDrString(dr,"onum");
                    bean.pallet = getDrString(dr,"panname"); 
                    panonum.Add(bean.buffer, bean);
                }
            }
            dr.Close();
            if (mysqlconchaxun != null)
            {
                mysqlconchaxun.Close();
            }
            return panonum;
        }

        //删除包列表中节号
        public void deletebatch(String batch)
        {
            var sql = "DELETE FROM package WHERE batch = "+batch+";";
            INSERT(sql);
        }

        public void cleanPackage(List<String> batchs)
        {
            if (batchs.Count == 0)
            {
                return;
            }
            string batch_name = "";
            for (int i = 0; i < batchs.Count; i++)
            {
                batch_name = batch_name + "'" + batchs[i] + "'";
                if(i < batchs.Count - 1)
                {
                    batch_name = batch_name + ",";
                }

            }
            var sql = "DELETE FROM package WHERE batch not in (" + batch_name + ");";
            INSERT(sql);
        }
        //写入账号信息
        public void adduser(User user)
        {
            var sqlstring = new sqlstring();
            
            
            var bean = readusersql();

            if (bean.username == null)
            {
                List<intobean> infobeans = new List<intobean>();
                infobeans.Add(new intobean("username", user.username));
                infobeans.Add(new intobean("password", user.password));
                infobeans.Add(new intobean("session_name", user.session_name));
                infobeans.Add(new intobean("token", user.token));
                infobeans.Add(new intobean("cookie", user.cookie));
                sqlstring.addinfos(infobeans);
                var sql = sqlstring.instersql("user");
                INSERT(sql);
            }
            else
            {
                sqlstring.addinfo("username", user.username);
                sqlstring.addinfo("password", user.password);
                sqlstring.addinfo("session_name", user.session_name);
                sqlstring.addinfo("token", user.token);
                sqlstring.addinfo("cookie", user.cookie);
                sqlstring.addwhere("id = 1");
                var sql = sqlstring.updatesql("user");
                INSERT(sql);
            }
        }
        //第一次打开添加盘数据
        public void firstwritePan(Dictionary<String, panbean> pans)
        {
            var sqlstring = new sqlstring();
            foreach (KeyValuePair<String, panbean> p in pans)
            {
                List<intobean> infobeans = new List<intobean>();
                infobeans.Add(new intobean("buffer", p.Value.buffer));
                infobeans.Add(new intobean("panname", p.Value.pallet));
                infobeans.Add(new intobean("onum", p.Value.onum));

                sqlstring.addinfos(infobeans);
            }

            //sqlstring.addwhere("buffer = " + pan.buffer);
            //sqlstring.addDuplicate("pallet = value(pallet),onum = value(onum)");
            var sql = sqlstring.instersql("pallet");
            INSERT(sql);
            
        }
        /**修改缓冲仓位数据*/
        public void writeBuffer(int id ,String buffer)
        {
            var sqlstring = new sqlstring();
            sqlstring.addinfo("buffer", buffer);
            sqlstring.addwhere("id = " + id);
            //sqlstring.addDuplicate("pallet = value(pallet),onum = value(onum)");
            var sql = sqlstring.updatesql("pallet");
            INSERT(sql);

        }
        //写入盘位数据
        public void writePan(panbean pan)
        {
            var sqlstring = new sqlstring();
            sqlstring.addinfo("buffer", pan.buffer);
            sqlstring.addinfo("panname", pan.pallet);
            sqlstring.addinfo("onum", pan.onum);
            sqlstring.addwhere("buffer = "+ pan.buffer);
            //sqlstring.addDuplicate("pallet = value(pallet),onum = value(onum)");
            var sql = sqlstring.updatesql("pallet");
            INSERT(sql);
  
        }

        //写入包数据
        public void writePackage(packagebean package)
        {
            var sqlstring = new sqlstring();
            List<intobean> infobeans = new List<intobean>();
            infobeans.Add(new intobean("batch", package.batch));
            infobeans.Add(new intobean("onum", package.onum));
            infobeans.Add(new intobean("pcode", package.pcode));
            infobeans.Add(new intobean("pid", package.pid));
            infobeans.Add(new intobean("pallet_id", package.pallet_id));
            infobeans.Add(new intobean("deliver_time", package.deliver_time));
            infobeans.Add(new intobean("zp_ctime", package.zp_ctime));
            infobeans.Add(new intobean("pcpid", package.pcpid));
            infobeans.Add(new intobean("pallet_num", package.pallet_num));
            infobeans.Add(new intobean("warehousing_time", package.warehousing_time));
            sqlstring.addinfos(infobeans);
            sqlstring.addDuplicate("batch = VALUES(batch),onum = VALUES(onum),pcode = VALUES(pcode)," +
                "pallet_id = VALUES(pallet_id),deliver_time = VALUES(deliver_time),zp_ctime = VALUES(zp_ctime),pcpid = VALUES(pcpid),warehousing_time = VALUES(warehousing_time)");
            var sql = sqlstring.instersql("package");
            INSERT(sql);
        }

        //写入多条包数据
        public void writePackages(Dictionary<String, packagebean> packages)
        {
            var sqlstring = new sqlstring();
            foreach (KeyValuePair<String, packagebean> p in packages)
            {
                List<intobean> infobeans = new List<intobean>();
                infobeans.Add(new intobean("batch", p.Value.batch));
                infobeans.Add(new intobean("onum", p.Value.onum));
                infobeans.Add(new intobean("pcode", p.Value.pcode));
                infobeans.Add(new intobean("pid", p.Value.pid));
                infobeans.Add(new intobean("pallet_id", p.Value.pallet_id));
                infobeans.Add(new intobean("deliver_time", p.Value.deliver_time));
                infobeans.Add(new intobean("zp_ctime", p.Value.zp_ctime));
                infobeans.Add(new intobean("pcpid", p.Value.pcpid));
                infobeans.Add(new intobean("pallet_num", p.Value.pallet_num));
                infobeans.Add(new intobean("warehousing_time", p.Value.warehousing_time));
                sqlstring.addinfos(infobeans);
            }
            sqlstring.addDuplicate("batch = VALUES(batch),onum = VALUES(onum),pcode = VALUES(pcode)," +
                "pallet_id = VALUES(pallet_id),deliver_time = VALUES(deliver_time),zp_ctime = VALUES(zp_ctime),pcpid = VALUES(pcpid),warehousing_time = VALUES(warehousing_time)");
            var sql = sqlstring.instersql("package");
            INSERT(sql);

        }


        //写入记录数据
        public void write_scan_record(string scan_string, string describe)
        {
            var sqlstring = new sqlstring();
            List<intobean> infobeans = new List<intobean>();
            infobeans.Add(new intobean("scan_string", scan_string));
            infobeans.Add(new intobean("ctime", GetTimeStamp()));
            infobeans.Add(new intobean("miaoshu", describe));
            sqlstring.addinfos(infobeans);
            var sql = sqlstring.instersql("scan_record_log");
            INSERT(sql);

        }

        //读取记录数据
        public void read_scan_record(string scan,Dictionary<String, scanRecordBean> scanRecordBeans,String sttime , String edtime)
        {
            if (scan == "" && (sttime == "" || edtime == ""))
            {
                return;
            }
            var sql = "select * from scan_record_log where ";
            if (scan != "")
            {
                sql = sql+ "scan_string like '%" + scan + "%'";
            }
            if (sttime != "" && edtime != "")
            {
                if (scan != "")
                {
                    sql = sql + " AND ";
                }
                sql = sql + " ctime between " + sttime + " and " + edtime;
            }
            sql = sql + ";";
                
            var dr = SELECT(sql);
            if (dr.HasRows)
            {
                //逐行读取数据输出
                while (dr.Read())
                {
                    var bean = new scanRecordBean();
                    bean.id = getDrString(dr, "id");
                    bean.scan_string = getDrString(dr, "scan_string");
                    bean.ctime = getDrString(dr, "ctime");
                    bean.miaoshu = getDrString(dr, "miaoshu");
                    scanRecordBeans.Add(bean.id, bean);
                }
            }
            dr.Close();
            if (mysqlconchaxun != null)
            {
                mysqlconchaxun.Close();
            }

        }
        //清除package表废弃数据
        public void deletepackage()
        {
            var sql = "DELETE FROM package WHERE pallet_id = 0 AND warehousing_time is not NULL;";
            INSERT(sql);
        }

        //清除log表废弃数据
        public void deletelog()
        {
            var time = Convert.ToInt64(GetTimeStamp()) - 5184000;
            var sql = "DELETE FROM scan_record_log WHERE ctime < " + time + ";";
            INSERT(sql);
        }

        //运行sql语句
        public void INSERT(string sql)
        {
            Console.WriteLine("sql===================" + sql);
            lock (this)
            {
    
                if (mysqlcon == null)
                {
                    mysqlcon = new MySqlConnection(M_str_sqlcon);
                }
                if (mysqlcon.State == ConnectionState.Closed)
                {
                    mysqlcon.Open();
                }
                //yanzhengMysql();
                using (MySqlCommand cmd = new MySqlCommand(sql, mysqlcon))
                {
                    cmd.ExecuteNonQuery();
                }
                //读出数据
                if (mysqlcon != null || mysqlcon.State == ConnectionState.Open)
                {
                    mysqlcon.Close();
                }
            }
        }
        //读取数据
        public MySqlDataReader SELECT(string sql)
        {
            
            Console.WriteLine("sql===================" + sql);
            lock (this)
            {
                if (mysqlconchaxun == null)
                {
                    mysqlconchaxun = new MySqlConnection(M_str_sqlcon);
                }
                if (mysqlconchaxun.State == ConnectionState.Closed)
                {
                    mysqlconchaxun.Open();
                }
                //yanzhengMysql();
                MySqlCommand mysqlcom = new MySqlCommand(sql, mysqlconchaxun);
                //读出数据
                MySqlDataReader dr = mysqlcom.ExecuteReader();  
                return dr;
            }
            
        }

        /// <summary>
        /// 获取时间戳
        /// </summary>
        /// <returns></returns>
        public static string GetTimeStamp()
        {
            TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds).ToString();
        }
    }
}
